package com.bk.dao;

import com.bk.model.Book;
import com.bk.model.BookType;
import com.bk.model.User;
import com.bk.util.DbUtil;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Ayi
 */
public class BookTypeDao {
    // 获取数据库中的图书类型表，以结果集返回
    public ResultSet getBookTypeTable() throws SQLException, ClassNotFoundException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from booktype";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        return rs;
    }

    // 往数据库添加一个新的图书类型
    public boolean addBookType(String btName) throws SQLException {
        if (btName.isEmpty()) {
            return false;
        }
        Connection connection = DbUtil.getConnection();
        String sql = "insert into booktype values (null, ?, ?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, btName);
        ps.setString(2, "0");
        try {
            ps.executeUpdate();
            return true;
        }catch (MySQLIntegrityConstraintViolationException e) {
            return false;
        }
    }

    // 根据id查找一个图书类型
    public BookType searchBookTypeById(String idOrNameInfo) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from booktype where btid = " + idOrNameInfo;
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            BookType resBookType = new BookType(rs.getString("btname"));
            resBookType.setBtid(Integer.valueOf(rs.getString("btid")));
            resBookType.setBtinventory(Integer.valueOf(rs.getString("btinventory")));
            return resBookType;
        }else{
            return null;
        }
    }

    // 根据name查找一个图书类型
    public BookType searchBookTypeByName(String idOrNameInfo) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from booktype where btname = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, idOrNameInfo);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            BookType resBookType = new BookType(rs.getString("btname"));
            resBookType.setBtid(Integer.valueOf(rs.getString("btid")));
            resBookType.setBtinventory(Integer.valueOf(rs.getString("btinventory")));
            return resBookType;
        }else{
            return null;
        }
    }

    // 从数据库删除一个新的图书类型
    public boolean deleteBookType(BookType booktype) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "delete from booktype where btid = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, Integer.toString(booktype.getBtid()));
        new BookDao().updateBook0(booktype.getBtname());
        return ps.executeUpdate() > 0 ? true : false;
    }

    // 编辑数据库的某个图书类型
    public boolean editBookType(BookType booktype, String btname) throws SQLException {
        Connection connection = DbUtil.getConnection();

        String sql0 = "update booktype set btname = ? where btid = ?";
        PreparedStatement ps0 = connection.prepareStatement(sql0);
        ps0.setString(1, btname);
        ps0.setString(2, Integer.toString(booktype.getBtid()));

//        String sql1 = "update booktype set btinventory = ? where btid = ?";
//        PreparedStatement ps1 = connection.prepareStatement(sql1);
//        ps1.setString(1, Integer.toString(btinventory));
//        ps1.setString(2, Integer.toString(booktype.getBtid()));

        String sql2 = "update book set btype = ? where btype = ?";
        PreparedStatement ps2 = connection.prepareStatement(sql2);
        ps2.setString(1, btname);
        ps2.setString(2, booktype.getBtname());

        int r0 = ps0.executeUpdate();
//        int r1 = ps1.executeUpdate();
        int r2 = ps2.executeUpdate();

        return r0 > 0 && r2 > 0 ? true : false;
    }

    // 得到所有的图书类型，以List的形式返回
    public List<String> getAllBookType() throws SQLException, ClassNotFoundException {
        ResultSet rs = getBookTypeTable();
        List<String> list = new ArrayList<>();
        while (rs.next()) {
            list.add(rs.getString("btname"));
        }
        return list;
    }

    // 上架一本书，更新对应图书类型的库存量
    public void updateAddInventory(int binventory, String btype) throws SQLException {
        Connection connection = DbUtil.getConnection();

        String s = "select * from booktype where btname = ?";
        PreparedStatement p = connection.prepareStatement(s);
        p.setString(1, btype);
        ResultSet r = p.executeQuery();
        if (r.next()) {
            int currentInventory = Integer.valueOf(r.getString("btinventory"));
            String sql = "update booktype set btinventory = ? where btname = ?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, Integer.toString(currentInventory + binventory));
            ps.setString(2, btype);
            ps.executeUpdate();
        }
    }

    // 下架一本书，更新对应图书类型的库存量
    public void updateSubInventory(int binventory, String btype) throws SQLException {
        Connection connection = DbUtil.getConnection();

        String s = "select * from booktype where btname = ?";
        PreparedStatement p = connection.prepareStatement(s);
        p.setString(1, btype);
        ResultSet r = p.executeQuery();
        if (r.next()) {
            int currentInventory = Integer.valueOf(r.getString("btinventory"));
            String sql = "update booktype set btinventory = ? where btname = ?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, Integer.toString(currentInventory - binventory));
            ps.setString(2, btype);
            ps.executeUpdate();
        }
    }

    // 编辑一本书，更新对应图书类型的库存量
    public void updateInventory(int binventory0, String btype, int binventory1) throws SQLException {
        updateSubInventory(binventory0, btype);
        updateAddInventory(binventory1, btype);
    }

    // 借阅界面的借阅功能 更新图书类型表
    public void updateSubBookType(Book book) throws SQLException {
        Connection connection = DbUtil.getConnection();

        String s = "select * from booktype where btname = ?";
        PreparedStatement p = connection.prepareStatement(s);
        p.setString(1, book.getBtype());
        ResultSet r = p.executeQuery();
        if (r.next()) {
            String sql = "update booktype set btinventory = ? where btname = ?";
            PreparedStatement ps = connection.prepareStatement(sql);
            int currentBookTypeNum = Integer.valueOf(r.getString("btinventory"));
            ps.setString(1, Integer.toString(currentBookTypeNum - 1));
            ps.setString(2, book.getBtype());
            ps.executeUpdate();
        }
    }

    // 借阅界面的退订功能 更新图书类型表
    public void updateAddBookType(Book book, int uborrow) throws SQLException {
        Connection connection = DbUtil.getConnection();

        String s = "select * from booktype where btname = ?";
        PreparedStatement p = connection.prepareStatement(s);
        p.setString(1, book.getBtype());
        ResultSet r = p.executeQuery();
        if (r.next()) {
            String sql = "update booktype set btinventory = ? where btname = ?";
            PreparedStatement ps = connection.prepareStatement(sql);
            int currentBookTypeNum = Integer.valueOf(r.getString("btinventory"));
            ps.setString(1, Integer.toString(currentBookTypeNum + uborrow));
            ps.setString(2, book.getBtype());
            ps.executeUpdate();
        }
    }
}
